#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys
from collections import defaultdict
from pytools import mysqltool
from load_fourthinfo_to_mysql import get_channel_info


def create_table(tablename):
    q = '''\
CREATE TABLE %s (
  stat_date DATE NOT NULL COMMENT '统计日期',

  dispcity1 varchar(50) DEFAULT '' COMMENT '一级城市',
  dispcity2 varchar(50) DEFAULT '' COMMENT '二级城市',
  dispcity3 varchar(50) DEFAULT '' COMMENT '三级城市',

  belong_cate1 varchar(50) DEFAULT '' COMMENT '一级类别',
  belong_cate2 varchar(50) DEFAULT '' COMMENT '二级类别',

  total_info_count INT DEFAULT 0 COMMENT '总帖子数',
  total_user_count INT DEFAULT 0 COMMENT '总发帖人数',

  info_count INT DEFAULT 0 COMMENT '总有效帖子数',
  user_count INT DEFAULT 0 COMMENT '总有效发帖人数',
  new_info_count INT DEFAULT 0 COMMENT '总新发帖子数',
  new_info_users INT DEFAULT 0 COMMENT '总新发帖子人数',
  modify_info_count INT DEFAULT 0 COMMENT '总修改帖子数',
  modify_info_users INT DEFAULT 0 COMMENT '总修改帖子人数',
  refresh_info_count INT DEFAULT 0 COMMENT '总刷新帖子数',
  refresh_info_users INT DEFAULT 0 COMMENT '总刷新帖子人数',
  new_valid_info_count INT DEFAULT 0 COMMENT '新增有效帖子数',
  new_valid_info_users INT DEFAULT 0 COMMENT '新增有效发帖人数',

  ordinary_info_count INT DEFAULT 0 COMMENT '个人有效帖子数',
  ordinary_info_users INT DEFAULT 0 COMMENT '个人有效帖子人数',
  ordinary_new_info_count INT DEFAULT 0 COMMENT '个人新发帖子数',
  ordinary_new_info_users INT DEFAULT 0 COMMENT '个人新发帖子人数',
  ordinary_modify_info_count INT DEFAULT 0 COMMENT '个人修改帖子数',
  ordinary_modify_info_users INT DEFAULT 0 COMMENT '个人修改帖子人数',
  ordinary_refresh_info_count INT DEFAULT 0 COMMENT '个人刷新帖子数',
  ordinary_refresh_users INT DEFAULT 0 COMMENT '个人刷新帖子人数',
  ordinary_new_valid_info_count INT DEFAULT 0 COMMENT '个人新增有效帖子数',
  ordinary_new_valid_info_users INT DEFAULT 0 COMMENT '个人新增有效发帖人数',

  vip_info_count INT DEFAULT 0 COMMENT 'VIP有效帖子数',
  vip_info_users INT DEFAULT 0 COMMENT 'VIP有效帖子人数',
  vip_new_info_count INT DEFAULT 0 COMMENT 'VIP新发帖子数',
  vip_new_info_users INT DEFAULT 0 COMMENT 'VIP新发帖子人数',
  vip_modify_info_count INT DEFAULT 0 COMMENT 'VIP修改帖子数',
  vip_modify_info_users INT DEFAULT 0 COMMENT 'VIP修改帖子人数',
  vip_refresh_info_count INT DEFAULT 0 COMMENT 'VIP刷新帖子数',
  vip_refresh_info_users INT DEFAULT 0 COMMENT 'VIP刷新帖子人数',
  vip_new_valid_info_count INT DEFAULT 0 COMMENT 'VIP新增有效帖子数',
  vip_new_valid_info_users INT DEFAULT 0 COMMENT 'VIP新增有效发帖人数',

  PRIMARY KEY (stat_date, dispcity1, dispcity2, dispcity3, belong_cate1, belong_cate2)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
''' % (tablename)
    dbh = mysqltool.connect_to_58_stats()
    # mysqltool.droptable(dbh, tablename)
    mysqltool.query(dbh, q)


if __name__ == '__main__':
    channel_info = set(get_channel_info().values())
    for c in channel_info:
        create_table(c + '_post_detail')
